/*
 * paymentDAO.java
 *
 * Created on March 17, 2007, 2:14 PM
 */

package edu.byu.isys413.mystuff;

import java.util.*;
import java.sql.*;

/**
 *
 * @author Sam Griffiths
 */
public class paymentDAO {
    
    //initialize variables
    private static paymentDAO instance;
    private String pmtid;
    private static Cache cache;
    
    /** Creates a new instance of paymentDAO */
    public paymentDAO() {
    cache = Cache.getInstance();
    }
        
    //returns instance of paymentDAO (singleton)
    public static synchronized paymentDAO getInstance() {
        if (instance == null) {
            instance = new paymentDAO();
            
        }
        return instance;
    }
    
    //creates a new paymentBO and sets its id to a unique guid
    public paymentBO create(){
        pmtid = Guid.generate();
        paymentBO pmt = new paymentBO(pmtid);
        cache.put(pmtid, pmt);
        return pmt;
    }
    
    /*
     *This method reads all payments from database and returns them in an ArrayList
     */
    public synchronized ArrayList<paymentBO> readALL() throws DataException {
        ArrayList<paymentBO> pmts = new ArrayList();
        Connection conn = null;
        try {
            //retrieve connection, create statement, and execute
            conn = ConnectionPool.getInstance().get();
            //System.out.println("Check");
            Statement st = conn.createStatement();
            //System.out.println("Executing Query");
            ResultSet rs = st.executeQuery("SELECT * FROM PAYMENT_TABLE");
            //System.out.println("Execute Succesful");
            
            while(rs.next()){
                //cycle through result set and initialize new bos, adding each one respectively to the ArrayList
                paymentBO pmt = new paymentBO(rs.getString("PAY_ID"));
                pmt.setAmountPaid(Double.valueOf(rs.getString("AMOUNT").trim()).doubleValue());//artist
                pmt.setChange(Double.valueOf(rs.getString("CHANGE").trim()).doubleValue());//album
                pmt.setPaymentType(rs.getString("TYPE"));
                pmt.setAlreadyInDatabase(true);
                pmts.add(pmt);
            }
            
            // release the connection
            conn.commit();
            ConnectionPool.getInstance().release(conn);
            
        }catch (ConnectionPoolException e){
            throw new DataException("Could not get a connection to the database.");
            
        }catch (SQLException e) {
            // rollback
            try {
                conn.rollback();
                ConnectionPool.getInstance().release(conn);
            }catch (SQLException e2) {
                throw new DataException("Big error: could not even release the connection", e2);
            }catch (ConnectionPoolException e3){
            }
            
            throw new DataException("Could not retrieve records", e);
        }
        
        return pmts;
    }
    
    //calles a different read handing in a connection and a payment ID
    public synchronized paymentBO read(String pmtid) throws DataException{
        paymentBO pmt = null;
        Connection conn = null;
        try {
            conn = ConnectionPool.getInstance().get();
            pmt = read(conn, pmtid);
            
            ///////////////////////////////////////////
            // call read with a connection (the other read method in this class)
            
            // release the connection
            conn.commit();
            ConnectionPool.getInstance().release(conn);
            
        }catch (ConnectionPoolException e){
            throw new DataException("Could not get a connection to the database.");
            
        }catch (SQLException e) {
            // rollback
            try {
                conn.rollback();
                ConnectionPool.getInstance().release(conn);
            }catch (SQLException e2) {
                throw new DataException("Big error: could not even release the connection", e2);
            }catch (ConnectionPoolException e3){
            }
            
            throw new DataException("Could not retrieve records", e);
        }
        
        return pmt;
    }
    
    //just like readALL, but only handles one object
    public synchronized paymentBO read(Connection conn, String id){
       paymentBO pmt = null;
        try{
        Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT * FROM PAYMENT_TBL WHERE PAY_ID='"+id+"'");
            pmt = new paymentBO(id);
            while(rs.next()){
                
                pmt.setAmountPaid(Double.valueOf(rs.getString("AMOUNT").trim()).doubleValue());//artist
                pmt.setChange(Double.valueOf(rs.getString("CHANGE").trim()).doubleValue());//album
                pmt.setPaymentType(rs.getString("TYPE"));
                pmt.setAlreadyInDatabase(true);
                            
            }
       }catch(Exception e){}
       return pmt;
    }
    ///////////////update//////////////////
    
    /////////////save////////////////////
    public synchronized boolean save(paymentBO pmt) throws DataException {
       System.out.println("Save entrance accessed");
        Connection conn = null;
        try {
            
            conn = ConnectionPool.getInstance().get();
            if(pmt.isDirty())
                save(conn,pmt);
            pmt.setDirty(false);
            
            ///////////////////////////////////////////
            // call read with a connection (the other read method in this class)
            
            // release the connection
            conn.commit();
            ConnectionPool.getInstance().release(conn);
            
        }catch (ConnectionPoolException e){
            throw new DataException("Could not get a connection to the database.");
            
        }catch (SQLException e) {
            // rollback
            try {
                conn.rollback();
                ConnectionPool.getInstance().release(conn);
            }catch (SQLException e2) {
                throw new DataException("Big error: could not even release the connection", e2);
            }catch (ConnectionPoolException e3){
            }
            
            throw new DataException("Could not retrieve records", e);
        }
        
        return true;
        }
    
    // takes in a payment object, checks to see if it's alreadyin the Database,
    //if yes then create an update sql statement and execute
    //if no then create an insert sql statement and execute
    public synchronized void save(Connection conn, paymentBO pmt){
        System.out.println("Second Save Accessed");
        try{
            Statement st = conn.createStatement();
            String payid = "";
           
            System.out.println("First Query Skipped");
            if(false == pmt.isAlreadyInDatabase()){
                
                String queryString = "INSERT INTO PAYMENT_TABLE VALUES (?,?,?,?)";
                PreparedStatement pstmt = conn.prepareStatement(queryString);
               
                //String aString = Double.toString(aDouble);
                pstmt.setString(1,pmt.getPaymentId());
                pstmt.setString(2,Double.toString(pmt.getAmountPaid()));
                pstmt.setString(3,Double.toString(pmt.getChange()));
                pstmt.setString(4,pmt.getPaymentType());
                pstmt.execute();
                pstmt.close();
                pmt.setAlreadyInDatabase(true);
            } else{
            
                String queryString = "UPDATE PAYMENT_TABLE SET AMOUNT = ?, CHANGE = ? , TYPE = ? WHERE PAY_ID = ? ";
                PreparedStatement pstmt = conn.prepareStatement(queryString);
                pstmt.setString(1,Double.toString(pmt.getAmountPaid()));//name
                pstmt.setString(2,Double.toString(pmt.getChange()));
                pstmt.setString(3,pmt.getPaymentType());
                pstmt.setString(4,pmt.getPaymentId());
                pstmt.execute();
                pstmt.close();
                
            }
        }catch(Exception e){}
    }
    
    /////////////delete////////////////////////
    //ya we don't delete payments, ever-
    
}
